﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Entity;
namespace DAL
{
    public class NewsContent
    {
        DbCommon.DbHelper DbHelper = new DbCommon.DbHelper();
        /// <summary>
        /// 得到NewsContent列表
        /// </summary>
        /// <returns>返回NewsContent列表</returns>
        public DataTable GetNewsContent() {
            DataTable dt = new DataTable();
            string sqlstr = "select * from NewsContent where NewsIsShow= 'true'";
            dt = DbHelper.ExcuteQuery(sqlstr);
            return dt;
        }

          /// <summary>
        /// 修改一条数据
        /// </summary>
        /// <param name="model">新闻模型</param>
        public int UpdateRow(BoNewsContent model)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("UPDATE [NewsContent] SET ");
            sql.Append("NewsTitle=@NewsTitle");
            sql.Append(",NewsAuthor=@NewsAuthor");
            sql.Append(",NewsClassId=@NewsClassId");
            sql.Append(",NewsArticle=@NewsArticle");
            sql.Append(",NewsImgUrl=@NewsImgUrl");
            sql.Append(",NewsIsShow=@NewsIsShow where NewsId = @NewsId  and NewsIsShow= 'true'");
            return DbHelper.ExcuteNoQuery(sql.ToString(), GetParameters(model));
        }


        /// <summary>
        /// 根据id获取一条数据模型,含有类型名称
        /// </summary>
        /// <param name="id">id号</param>
        /// <returns>返回获取到的数据</returns>
        public BoNewsContent GetNewsSingle(string id) {
            SqlParameter[] pr = new SqlParameter[]{
                new SqlParameter("@id",id)
            };
            string sqlstr = "select NewsId,NewsTitle,NewsAuthor,NewsClassId,NewsArticle,NewsImgUrl,NewsIsShow,NewspubTime,NCTitle,NCIsShow from v_news where NewsId = @id and NewsIsShow= 'true'";
            DataTable table= DbHelper.ExcuteQuery(sqlstr, pr);
            List<BoNewsContent> list = GetList(table);
            return list.Count > 0 ? list[0] : null;
        }
        /// <summary>
        /// 添加一条记录
        /// </summary>
        /// <param name="model">实体类</param>
        /// <returns></returns>
        public int Insert(BoNewsContent model)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("INSERT INTO [NewsContent]");
            sql.Append("([NewsTitle],[NewsAuthor],[NewsClassId],[NewsArticle],[NewsImgUrl],[NewsIsShow],[NewsId])");
            sql.Append("VALUES");
            sql.Append("(@NewsTitle,@NewsAuthor,@NewsClassId,@NewsArticle,@NewsImgUrl,@NewsIsShow,@NewsId)");
            return DbHelper.ExcuteNoQuery(sql.ToString(), GetParameters(model));
        }

        /// <summary>
        /// 获取参数
        /// </summary>
        /// <param name="model">实体类</param>
        /// <returns></returns>
        private SqlParameter[] GetParameters(BoNewsContent model)
        {
            SqlParameter[] parameters = new SqlParameter[] {
                new SqlParameter("@NewsTitle",SqlDbType.NVarChar,50),
                new SqlParameter("@NewsAuthor",SqlDbType.NVarChar,50),
                new SqlParameter("@NewsClassId",SqlDbType.NVarChar,50),
                new SqlParameter("@NewsArticle",SqlDbType.Text),
                new SqlParameter("@NewsImgUrl",SqlDbType.NVarChar,200),
                new SqlParameter("@NewsIsShow",SqlDbType.Bit),
                new SqlParameter("@NewsId",SqlDbType.NVarChar,50),
            };
            parameters[0].Value = model.NewsTitle;
            parameters[1].Value = model.NewsAuthor;
            parameters[2].Value = model.NewsClassId;
            parameters[3].Value = model.NewsArticle;
            parameters[4].Value = model.NewsImgUrl;
            parameters[5].Value = model.NewsIsShow;
            parameters[6].Value = model.NewsId;
            return parameters;
        }
        /// <summary>
        /// 删除一行
        /// </summary>
        /// <param name="id">此行的id</param>
        /// <returns>返回删除的行数</returns>
        public int DelData(string id) {
            int i = 0;
            SqlParameter[] pr = new SqlParameter[]{
              new SqlParameter("@id",id)  
            };
            string sqlstr = "update NewsContent set NewsIsShow='false' where NewsId = @id";
            i = DbHelper.ExcuteNoQuery(sqlstr,pr);
            return i;
        }

          /// <summary>
        /// 通过竞赛id来删除与之相对应的内容信息
        /// </summary>
        /// <param name="classId"></param>
        /// <returns>返回受影响的行数</returns>
        public int DeleteByClassId(string classId)
        {
            SqlParameter[] parameter = new SqlParameter[] { 
               new SqlParameter("@NewsClassId",classId),
            };
            string sqlstr = "update NewsContent set NewsIsShow='false' where NewsClassId=@NewsClassId";
            return DbHelper.ExcuteNoQuery(sqlstr,parameter);
        }
        /// <summary>
        /// 根据条件查询前几行数据
        /// </summary>
        /// <param name="rowNb">行数</param>
        /// <param name="sqlCon">查询条件</param>
        /// <param name="orderCon">排序条件</param>
        /// <returns>返回前几行数据</returns>
        public DataTable GetRows(int rowNb, string sqlCon, string orderCon) {

            StringBuilder sbd = new StringBuilder();
            sbd.Append("select top ");
            sbd.Append(rowNb);
            sbd.Append(" * from NewsContent where 1=1 and NewsIsShow= 'true' and ");
            if(!string.IsNullOrEmpty(sqlCon))
                sbd.Append(sqlCon);
            if (!string.IsNullOrEmpty(orderCon))
            {
                sbd.Append(" order by ");
                sbd.Append(orderCon);
            }
            DataTable dt = DbHelper.ExcuteQuery(sbd.ToString());
            return dt;
        }
        /// <summary>
        /// 根据视图得到所有的新闻列表
        /// </summary>
        /// <returns></returns>
        public List<BoNewsContent> GetNews(object sqlcon)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select NewsId,NewsTitle,NewsAuthor,NewsClassId,NewsArticle,NewsImgUrl,NewsIsShow,NewspubTime,NCTitle,NCIsShow from v_news where 1 = 1 and NewsIsShow= 'true' ");
            if (sqlcon != null && !string.IsNullOrEmpty(sqlcon.ToString()))
            {
                sql.Append("and ");
                sql.Append(sqlcon);
            }
            DataTable table = DbHelper.ExcuteQuery(sql.ToString());
            List<BoNewsContent> list = GetList(table);
            return list.Count > 0 ? list : null;
        }
        /// <summary>
        /// 将表格转换成list
        /// </summary>
        /// <param name="table"></param>
        /// <returns></returns>
        private List<BoNewsContent> GetList(DataTable table)
        {
            List<BoNewsContent> list = new List<BoNewsContent>();
            if (table != null && table.Rows.Count > 0)
            {

                for (int i = 0; i < table.Rows.Count; i++)
                {
                    BoNewsContent model = new BoNewsContent();
                    model.NewsId = (string)table.Rows[i]["NewsId"];
                    model.NewsTitle = (string)table.Rows[i]["NewsTitle"];
                    model.NewsAuthor = (string)table.Rows[i]["NewsAuthor"];
                    model.NewsClassId = (string)table.Rows[i]["NewsClassId"];
                    model.NewsArticle = (string)table.Rows[i]["NewsArticle"];
                    model.NewsImgUrl = (string)table.Rows[i]["NewsImgUrl"];
                    model.NewsIsShow = (bool)table.Rows[i]["NewsIsShow"];
                    model.NewspubTime = (DateTime)table.Rows[i]["NewspubTime"];
                    model.NCTitle = (string)table.Rows[i]["NCTitle"];
                    model.NCIsShow = (bool)table.Rows[i]["NCIsShow"];
                    list.Add(model);
                }
            }
            return list;
        }
         /// <summary>
        /// 根据数据行数获取表格
        /// </summary>
        /// <param name="start">开始数据条数</param>
        /// <param name="end">结束数据集条数</param>
        /// <param name="sqlcon">查询条件</param>
        /// <returns>返回相应的表格</returns>
        public DataTable GetPage(string startNum, string endNum, string orderCon, string sqlCon)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select * from (select *,Row_Number() over ( order by ");
            sql.Append(orderCon);
            sql.Append(" )rownum from NewsContent where 1=1 and NewsIsShow= 'true' ");
            if (!string.IsNullOrEmpty(sqlCon))
            {
                sql.Append("and ");
                sql.Append(sqlCon);
            }
            sql.Append(" ) t where t.rownum>");
            sql.Append(startNum);
            sql.Append(" and t.rownum<=");
            sql.Append(endNum);
            return DbHelper.ExcuteQuery(sql.ToString());
        }
    }
}
